# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
from .settings import MY_SQL


class RewardPipeline:
    count = 0

    def open_spider(self, spider):
        fl = open('./reward.csv', mode='a', encoding='utf-8')
        fl.write("期号, 红球, 蓝球\n")
        self.fl = fl

    def close_spider(self, spider):
        if self.fl:
            self.fl.close()

    def process_item(self, item, spider):
        self.fl.write(f"{item['qi_hao']}, {'_'.join(item['red_list'])},{'_'.join(item['blue_list'])}\n")
        return item


class RewardMySQLPipeline:

    def open_spider(self, spider):
        self.conn = pymysql.connect(host=MY_SQL['host'],
                                    port=MY_SQL['port'],
                                    user=MY_SQL['user'],
                                    password=MY_SQL['password'],
                                    db=MY_SQL['db'],
                                    charset=MY_SQL['charset'])

    def close_spider(self, spider):
        if self.conn:
            self.conn.close()

    def process_item(self, item, spider):
        try:
            cursor = self.conn.cursor()
            sql = """
            INSERT INTO `caipiao`   (qi_hao,red_list,blue_list) VALUES (%s,%s,%s)
            """
            cursor.execute(sql, (item['qi_hao'], '_'.join(item['red_list']), '_'.join(item['blue_list'])))
            self.conn.commit()
        except Exception:
            # 事务回滚
            self.conn.rollback()
        finally:
            if cursor:
                cursor.close()
        return item
